<?php
include "include/config.inc.php";
$startDateSelect   = date("Y-m-d", strtotime('today'));
$endDateSelect     = date("Y-m-d", strtotime('today'));
$party             = array();
$partySelect       = 0;
$account1Select     = 0;
$account2Select     = 0;
$creditCount       = 0;
$debitCount        = 0;
$creditTotal       = 0;
$debitTotal        = 0;
$creditArray       = array();
$debitArray        = array();
$accountIdSelected = "";
$msg = "";

if(isset($_POST['go']))
{
  $partySelect     = $_REQUEST['party'];
  $account1Select   = $_REQUEST['accountId1'];
  $account2Select   = $_REQUEST['accountId2'];
  $startDateSelect = $_REQUEST['startDateYear']."-".$_REQUEST['startDateMonth']."-".$_REQUEST['startDateDay'];
  $endDateSelect   = $_REQUEST['endDateYear']."-".$_REQUEST['endDateMonth']."-".$_REQUEST['endDateDay'];
  $startDate  = $_POST['startDateYear']."-".$_POST['startDateMonth']."-".$_POST['startDateDay'];
  $endDate    = $_POST['endDateYear']."-".$_POST['endDateMonth']."-".$_POST['endDateDay'];
  
  //From Loan Table :Start
  if($account1Select == 0 || $account1Select == -1 || $account1Select == 1 || $account1Select == 2 || $account1Select == 15)
  { //0 = All             || -1 = Profit Loss      || 1 = Loan             || 2 = Interest Received || 15 = Ashapura
    $selectloan = "SELECT loanId, loanDate, loanAmount, interest, partyName
                     FROM loan
                LEFT JOIN party ON loan.partyId = party.partyId
                    WHERE loanDate  BETWEEN '".$startDate."' AND  '".$endDate."'";
    if($partySelect > 0)
    {
    	$selectloan .= " AND loan.partyId = ".$_REQUEST['party'];
    }
    $selecttransactionRes = mysql_query($selectloan);
    while($selecttransactionResRow = mysql_fetch_array($selecttransactionRes))
	  {
      if($account1Select == 0 || $account1Select == 1 || $account1Select == 15)
      {
	  	  //Loan : Credit From Ashapura :start
        $creditTotal                         += $selecttransactionResRow['loanAmount'];
        $creditArray[$creditCount]['amount']  = $selecttransactionResRow['loanAmount'];
        $creditArray[$creditCount]['desc']    = 'Loan To Party : '.$selecttransactionResRow['loanId']." : ".$selecttransactionResRow['partyName'];
        $creditArray[$creditCount]['date']    = date("d-m-Y",strtotime($selecttransactionResRow['loanDate']));
        $creditCount++;
	  	  //Loan : Credit From Ashapura :end
      }
      
      if($account1Select == 0 || $account1Select == -1 || $account1Select == 2)
      {
	  	  //Interest : Credit Interest Received: start
        $creditTotal                        += $selecttransactionResRow['interest'];
        $creditArray[$creditCount]['amount']  = $selecttransactionResRow['interest'];
        $creditArray[$creditCount]['desc']    = 'Interest From Party : '.$selecttransactionResRow['loanId']." : ".$selecttransactionResRow['partyName'];
        $creditArray[$creditCount]['date']    = date("d-m-Y",strtotime($selecttransactionResRow['loanDate']));
        $creditCount++;
	  	  //Interest : Credit Interest Received: end
	  	}
    }
  }
  //From Loan Table :End
  
  //From Installment Table :Start
  if($account1Select == 15 || $account1Select == 3)
  {
    $selectInterest = "SELECT loanId, installmentDate, installmentAmount
                         FROM installment
                        WHERE receiveDate BETWEEN '".$startDate."' AND  '".$endDate."'
                          AND isReceived = 'Y'";
    if($partySelect > 0)
    {
    	$selectInterest .= " AND loanId IN (SELECT loanId 
    	                                 FROM loan
    	                                WHERE partyId = ".$_REQUEST['party'].")";
    }
    $selectInterestRes = mysql_query($selectInterest);
    while($selectInterestResRow = mysql_fetch_array($selectInterestRes))
	  {
      $selectloan = "SELECT partyName
                       FROM loan
                  LEFT JOIN party ON loan.partyId = party.partyId
                      WHERE loanId = ".$selectInterestResRow['loanId'];
      $selecttransactionRes = mysql_query($selectloan);
      $installmentPartyName = "";
      while($selecttransactionResRow = mysql_fetch_array($selecttransactionRes))
  	  {
  	  	$installmentPartyName = $selecttransactionResRow['partyName'];
  	  }
  	  
    	//Installment : Credit start
      $debitTotal                        += $selectInterestResRow['installmentAmount'];
      $debitArray[$debitCount]['amount']  = $selectInterestResRow['installmentAmount'];
      $debitArray[$debitCount]['desc']    = 'Installment From Party : '.$selectInterestResRow['loanId']." : ".$installmentPartyName;
      $debitArray[$debitCount]['date']    = date("d-m-Y",strtotime($selectInterestResRow['installmentDate']));
      $debitCount++;
  	  //Interest : Credit end
    }
  }
  //From Installment Table :End
  
  //From Transaction Table :Start
	if($partySelect == 0)
	{
    $selecttransaction = "SELECT t.accountIdCr, t.accountIdDr, t.amount, a1.accountName AS accountNameCr, a2.accountName AS accountNameDr, 
                                 a1.inludeInPL AS inludeInPLCr, a2.inludeInPL AS inludeInPLDr,
                                 t.amount, t.transDate, t.narration, interest30days
                            FROM transaction t
                            LEFT JOIN account a1 ON a1.accountId = t.accountIdCr
                            LEFT JOIN account a2 ON a2.accountId = t.accountIdDr
                          WHERE 1 = 1";
    if($account1Select != 0 && $account1Select != -1)
    {
      $selecttransaction .= " AND (   t.accountIdCr =  ".$account1Select."
                                   OR t.accountIdDr =  ".$account1Select."
                                  )";
    }
    if($account2Select != 0 && $account2Select != -1)
    {
      $selecttransaction .= " AND (   t.accountIdCr =  ".$account2Select."
                                   OR t.accountIdDr =  ".$account2Select."
                                  )";
    }
    if($account1Select == -1)
    {
      $selecttransaction .= " AND (   t.accountIdCr IN (SELECT accountId
                                                    FROM account
                                                   WHERE inludeInPL = 'Y')
                                   OR t.accountIdDr IN (SELECT accountId
                                                    FROM account
                                                   WHERE inludeInPL = 'Y')
                                  )";
    }

    $selecttransaction .= " AND t.transDate BETWEEN '".$startDate."' AND  '".$endDate."'";
                        
    $selecttransactionRes = mysql_query($selecttransaction);
    
    while($selecttransactionResRow = mysql_fetch_array($selecttransactionRes))
    {
    	if($selecttransactionResRow['accountIdCr'] == $account1Select && $account1Select != -1)
    	{
        $creditTotal                         += $selecttransactionResRow['amount'];
        $creditArray[$creditCount]['amount']  = $selecttransactionResRow['amount'];
        $creditArray[$creditCount]['desc']    = 'Credit : '.$selecttransactionResRow['accountNameCr']." & Debit : ".$selecttransactionResRow['accountNameDr']
                                                ." : ".$selecttransactionResRow['narration'];
        $creditArray[$creditCount]['date']    = date("d-m-Y",strtotime($selecttransactionResRow['transDate']));
        $creditCount++;
      }
    	elseif($selecttransactionResRow['accountIdDr'] == $account1Select && $account1Select != -1)
    	{
        $debitTotal                        += $selecttransactionResRow['amount'];
        $debitArray[$debitCount]['amount']  = $selecttransactionResRow['amount'];
        $debitArray[$debitCount]['desc']    = 'Credit :: '.$selecttransactionResRow['accountNameCr']." & Debit : ".$selecttransactionResRow['accountNameDr']
                                              ." : ".$selecttransactionResRow['narration'];
        $debitArray[$debitCount]['date']    =  date("d-m-Y",strtotime($selecttransactionResRow['transDate']));
        $debitCount++;
      }
      elseif($account1Select == -1 && $selecttransactionResRow['inludeInPLCr'] == 'Y')
      {
        $debitTotal                        += $selecttransactionResRow['amount'];
        $debitArray[$debitCount]['amount']  = $selecttransactionResRow['amount'];
        $debitArray[$debitCount]['desc']    = 'Credit ::: '.$selecttransactionResRow['accountNameCr']." & Debit : ".$selecttransactionResRow['accountNameDr']
                                                ." : ".$selecttransactionResRow['narration'];
        $debitArray[$debitCount]['date']    = date("d-m-Y",strtotime($selecttransactionResRow['transDate']));
        $debitCount++;
      }
      elseif($account1Select == -1 && $selecttransactionResRow['inludeInPLDr'] == 'Y')
      {
        $creditTotal                         += $selecttransactionResRow['amount'];
        $creditArray[$creditCount]['amount']  = $selecttransactionResRow['amount'];
        $creditArray[$creditCount]['desc']    = 'Credit :::: '.$selecttransactionResRow['accountNameCr']." & Debit : ".$selecttransactionResRow['accountNameDr']
                                              ." : ".$selecttransactionResRow['narration'];
        $creditArray[$creditCount]['date']    = date("d-m-Y",strtotime($selecttransactionResRow['transDate']));
        $creditCount++;
      }
    }
  }
  //From Transaction Table :End
}

$accountArr = array();
if(isset($_REQUEST['accounts']))
{
  $accountIdSelected = $_REQUEST['accounts'];
}
$selAccountQry="SELECT accountId,accountName
                  FROM account
                 ORDER BY orderBy, accountId";
$selAccountQryRes=mysql_query($selAccountQry);
$a1=0;
$a2=0;

//We want to display All option in 2nd combo, not in 1st 
$accountId2Values[$a2]   = '0';
$accountName2[$a2] = 'All';
$a2++;

while($selAccountQryResRow=mysql_fetch_array($selAccountQryRes))
{
	$accountId1Values[$a1]  = $selAccountQryResRow['accountId'];
	$accountName1[$a1]= $selAccountQryResRow['accountName'];
	$a1++;
	$accountId2Values[$a2]  = $selAccountQryResRow['accountId'];
	$accountName2[$a2]= $selAccountQryResRow['accountName'];
	$a2++;
}
$partyArr = array();
$p=0;
$selpartyQry="SELECT partyId,partyName
                FROM party
               ORDER BY partyName";
$selPartyQryRes=mysql_query($selpartyQry);
while($selPartyQryResRow=mysql_fetch_array($selPartyQryRes))
{
	$partyArr['partyId'][$p]   = $selPartyQryResRow['partyId'];
	$partyArr['partyName'][$p] = $selPartyQryResRow['partyName'];
	$p++;
	
}
if(isset($_REQUEST['msg']))
{
  $msg = $_REQUEST['msg'] ;
}
$grandTotal=$creditTotal - $debitTotal ;

$smarty->assign('accountIdSelected',$accountIdSelected);
$smarty->assign('accountId1Values',$accountId1Values);
$smarty->assign('accountId2Values',$accountId2Values);
$smarty->assign('account1Select',$account1Select);
$smarty->assign('account2Select',$account2Select);
$smarty->assign('accountName1',$accountName1);
$smarty->assign('accountName2',$accountName2);
$smarty->assign('partyArr',$partyArr);
$smarty->assign('partySelect',$partySelect);
$smarty->assign('startDateSelect',$startDateSelect);
$smarty->assign('endDateSelect',$endDateSelect);
$smarty->assign('creditArray',$creditArray);
$smarty->assign('debitArray',$debitArray);
$smarty->assign('msg',$msg);
$smarty->assign('creditTotal',$creditTotal);
$smarty->assign('debitTotal',$debitTotal);
$smarty->assign('grandTotal',$grandTotal);
$smarty->display('dispalyTransaction.tpl');
?>